package com.suitsoft.bdms.dao.account;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import com.suitsoft.bdms.entity.account.User;

import org.springside.modules.orm.Page;
import org.springside.modules.orm.hibernate.HibernateDao;

/**
 * 用户对象的泛型DAO类.
 * 
 * @author calvin
 */
@Component
public class UserDao extends HibernateDao<User, Long> {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	// 统一定义所有用户的HQL.
	private static final String QUERY_BY_ROLE_HQL = "select user from User user join user.roles as role where role.name=?";
	
	public User loadByLoginName(String userName) {
		return findUniqueBy("loginName", userName);
	}

	/**
	 * 查找拥有指定角色的用户.
	 */
	public List<User> getUsersByRole(String roleName) {
		return find(QUERY_BY_ROLE_HQL, roleName);
	}
	
	public List<Map<String,Object>> userList(){
		String sql = "select login_name loginName, password from acct_user where flag is null order by position";
		return jdbcTemplate.queryForList(sql);
	}
	
	@SuppressWarnings("unchecked")
	public List getTree(Long id){
		//子节点
		final List<String> listNodeID = new ArrayList<String>();
		//父节点
		final List<String> listParentID = new ArrayList<String>();
		//节点上显示的文字
		final List<String> listCaption = new ArrayList<String>();
		//url
		final List<String> listNodeURL = new ArrayList<String>();
		final List<String> listTarget = new ArrayList<String>();
		final List<String> listTipInfo = new ArrayList<String>();	
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select distinct NodeID, ParentID, Caption, NodeURL NodeURL, Target, TipInfo ");
		sql.append(" from acct_user_role usersRoles, ");
		sql.append(" acct_role_authority rolesAuthorities, ");
		sql.append(" acct_authority authorities, ");
		sql.append(" treeInfo ");
		sql.append(" where usersRoles.User_Id = " + id);
		sql.append(" and usersRoles.Role_Id = rolesAuthorities.Role_Id ");
		sql.append(" and authorities.id = rolesAuthorities.Authority_Id ");
		sql.append(" and authorities.group_id = treeInfo.Icon ");
		//sql.append(" and treeinfo.nodeid != 20000 ");
		
		Set set = new HashSet();
		StringBuilder sql2 = new StringBuilder(" select distinct * from (");
		@SuppressWarnings("unused")
		String sql0 = "";
		int len = 0;
		String sql1 = sql.toString();
		String temp="";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql1.toString());
		while(list.size() > 0){
			for(Map m : list  ){
				set.add(String.valueOf(m.get("ParentID")));
			}	
			Iterator it = set.iterator();
			while(it.hasNext()){
				temp = String.valueOf(it.next());
				sql0 = " select NodeID, ParentID, Caption, NodeURL, Target, TipInfo  from TreeInfo where TreeInfo.Nodeid =  "+ temp;
				sql2.append(" select NodeID, ParentID, Caption, NodeURL, Target, TipInfo  from TreeInfo where TreeInfo.Nodeid =  "+ temp + " union ");
			}
			set.clear();
			 list = jdbcTemplate.queryForList((sql2.toString() + sql + ")"));
			 if(len != list.size()){
				len = list.size();
			 }else{
				 break;				 
			 }
		}
		sql2.append( sql+ ")");
		jdbcTemplate.query(sql2.toString(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				listNodeID.add(String.valueOf(rs.getInt("NodeID")));
				listParentID.add(String.valueOf(rs.getInt("ParentID")));
				listCaption.add(rs.getString("Caption") == null ? "" : rs.getString("Caption").trim());
				listNodeURL.add(rs.getString("NodeURL") == null ? "" : rs.getString("NodeURL").trim());
				listTarget.add(rs.getString("Target") == null ? "" : rs.getString("Target").trim());
				listTipInfo.add(rs.getString("TipInfo") == null ? "" : rs.getString("TipInfo").trim());
				return null;
			}
		});
			List tempTree = new ArrayList();
			
			tempTree.add(listNodeID);
			tempTree.add(listParentID);
			tempTree.add(listCaption);
			tempTree.add(listNodeURL);
			tempTree.add(listTarget);
			tempTree.add(listTipInfo);
			
			return tempTree;
	}
	public Page<User> getAllUser(Page<User> page) {
		String hql = "from User where flag is null";
		return findPage(page, hql);
	}
	
	/**
	 * 获取 同步用户中的信息
	 * @param loginName
	 * @return
	 */
	public Map<String,Object> findSyncUser(String loginName) throws Exception{
		String sql ="SELECT * FROM SYNC_USER S WHERE S.LOGIN_NAME = '"+loginName+"'";
		return jdbcTemplate.queryForMap(sql);
	}
	
	/**
	 * 按 用户名 子系统ID获取用户的密码
	 * @param id
	 * @param systemId
	 * @return
	 */
	public List<Map<String,Object>> findSyncPasswordByUserId(Long id,Long systemId){
		String sql = " SELECT * FROM SYNC_PASSWRD D WHERE D.USER_ID = " + id;
		if(systemId != null){
			sql += " AND D.SYSTEM_ID = " + systemId;
		}
		return jdbcTemplate.queryForList(sql);
	}
	
}
